#use encoding 'utf-8';
$dbname =shift;
$maindir=shift;
use DBI;
#use CGI;

$dbistring ="dbi:ODBC:$dbname";
#$##maindir = $argv[1];
print "dbname $dbname $maindir\n";

my $dbh= DBI->connect ($dbistring,'','') || die "could not connect $dbistring\n";


my $query1="select distinct Panchayats.district_code,Panchayats.block_code,Panchayats.panchayat_code,Districts.district_name,Blocks.block_name,Panchayats.panchayat_name from 
(Panchayats inner join Districts 
on districts.district_code=panchayats.district_code)
inner join blocks  
on blocks.block_code=panchayats.block_code";

$sth=$dbh->prepare($query1);
$sth->execute();
my $district=0;
my $block=0;
my $panchayat=0;
my $count=0;
my %panchayats; #hash of hashes{ district_code=>{block_code=>(panchayat1,panchayat2,....),...},....}
%districts;
%blocks;
%panchayatnames;
while (@ary=$sth->fetchrow_array ()) {
  push @{$panchayats{$ary[0]}{$ary[1]}},$ary[2];
  $blocks{$ary[1]}=$ary[4];
  $panchayatnames{$ary[2]}=$ary[5];
  $districts{$ary[0]}=$ary[3];
 
}
my $agency_query ="select exe_agency_code,agency_name  from exe_agency";
my $sth2_agency = $dbh->prepare($agency_query);

$sth2_agency = $dbh->prepare($agency_query);
$sth2_agency->execute;
my %agencies;
my $count=0;
while (my @ar=$sth2_agency->fetchrow_array()) {
$count++;
my $x =$ar[1];
$x =~ s/\//\-/g;
$x =~ s/\./\-/g;
 $agencies{$ar[0]} = $x;
 
}
my $mustrol_query ="select distinct msr_no from UP40Mustroll0910";
my $sth_mustroll=$dbh->prepare($mustrol_query);
$sth_mustroll->execute();
my @mustroll;
while (my @arry=$sth_mustroll->fetchrow_array() ) {
push @mustroll, $arry[0];
}

#$maindir = "c:/nregaout";
open $fh, ">$maindir/index.html";
foreach $district(keys(%panchayats)) {
#my $distdir = $maindir."/".$districts{$district};
#mkdir $distdir;
print $fh "<p align=left><b>$districts{$district}</b>";
  foreach $block(keys %{$panchayats{$district}}) {
  print $fh "<p align=left><b>$blocks{$block}</b>";
  print $fh "<ul>";
 # $blockdir= $distdir."/".$blocks{$block};
  # if (! -d $blockdir) { mkdir $blockdir;}
  print $fh "<li> <a href='$districts{$district}/$blocks{$block}/abstract1_2008-2009_".$block.".html'>Abstract(2008-2009)</a>";
  print $fh "<li> <a href='$districts{$district}/$blocks{$block}/abstract1_2009-2010_".$block.".html'>Abstract(2009-2010)</a>";
  print $fh "<li>Works (2008-09): ";
	foreach $panchayat(@{$panchayats{$district}{$block}}) {
	print $fh " <a href='"."$districts{$district}/$blocks{$block}/works_0809_".$panchayatnames{$panchayat}.".html'".">$panchayatnames{$panchayat} <a>,";
	}
	print $fh "<li>Works (2009-10): ";
	foreach $panchayat(@{$panchayats{$district}{$block}}) {
	print $fh " <a href='"."$districts{$district}/$blocks{$block}/works_0910_".$panchayatnames{$panchayat}.".html'".">$panchayatnames{$panchayat} <a>,";
	}
	 print $fh "<li>Workers : ";
	foreach $panchayat(@{$panchayats{$district}{$block}}) {
	print $fh " <a href='"."$districts{$district}/$blocks{$block}/"."workers_".$panchayatnames{$panchayat}.".html'".">$panchayatnames{$panchayat} <a>,";

	}
	
}
	#	$panchayatdir= $blockdir."/".$panchayatnames{$panchayat};
	foreach my $key(keys %agencies) {
	 print $fh "<li> <a href='"."$districts{$district}/$blocks{$block}/".$blocks{$block}."_works_panchayatwise".$agencies{$key}."_2009-2010.html'".">$agencies{$key} <a>,";
	}
	foreach my $key(keys %agencies) {
	 print $fh "<li> <a href='"."$districts{$district}/$blocks{$block}/".$blocks{$block}."_works_panchayatwise".$agencies{$key}."_2008-2009.html'".">$agencies{$key} <a>,";
	}
	print $fh "<li>";
	foreach my $mustroll(@mustroll) {
	 print $fh "<a href='"."$districts{$district}/muster rolls/"."$mustroll.html'".">$mustroll <a>,";
	}
	
       
}